﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;


namespace KPIS.GERP.GHRMS.DAL
{
    public class HRMKeyPerformanceAreaListDAL : IHRMKeyPerformanceAreaListDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable ListKeyArea()
        {
            string strSQL = String.Empty;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT key_area_seq, key_area_name "
                             + " FROM hrm_mas_key_area "
                             + " WHERE isactive=1"
                             + " AND record_status not like @status ";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadGridKeyArea(int pos_seq)
        {
            string strSQL = String.Empty;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pos_seq", pos_seq);

                strSQL = " SELECT pa.kpa_seq, pa.pos_seq, pa.key_area_seq, pa.description, pa.weight, ka.key_area_name "
                                 + " FROM hrm_perf_key_performance_area pa "
                                 + " INNER JOIN hrm_mas_key_area ka ON pa.key_area_seq = ka.key_area_seq "
                                 + " WHERE pa.pos_seq = @pos_seq "
                                 + " AND pa.isactive=1 "
                                 + " AND pa.record_status not like @status ";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadKeyAreaDetail(int intKpaSeq)
        {
            string strSQL = String.Empty;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@kpa_seq", intKpaSeq);

                strSQL = " SELECT key_area_seq, description, weight "
                             + " FROM hrm_perf_key_performance_area "
                             + " WHERE kpa_seq = @kpa_seq "
                             + " AND isactive=1 "
                             + " AND record_status not like @status ";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateKeyArea(int kpa_seq, int key_area, string description, Nullable<int> weight, string create_by)
        {
            string strSQL = String.Empty;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@kpa_seq", kpa_seq);
                dbManager.AddParameters(2, "@key_area", key_area);
                dbManager.AddParameters(3, "@description", description);
                dbManager.AddParameters(4, "@weight", weight);
                dbManager.AddParameters(5, "@updated_by", create_by);

                strSQL = " UPDATE hrm_perf_key_performance_area  "
                             + " SET key_area_seq = @key_area, description = @description, weight=@weight, "
                             + " updated_by = @updated_by, updated_when = GETDATE() "
                             + " WHERE kpa_seq = @kpa_seq "
                             + " AND isactive=1 "
                             + " AND record_status not like @status ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertKeyArea(int pos_seq, int key_area, string description, Nullable<int> weight, string create_by)
        {
            string strSQL = String.Empty;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@pos_seq", pos_seq);
                dbManager.AddParameters(2, "@key_area", key_area);
                dbManager.AddParameters(3, "@description", description);
                dbManager.AddParameters(4, "@weight", weight);
                dbManager.AddParameters(5, "@create_by", create_by);

                strSQL = " INSERT INTO hrm_perf_key_performance_area  "
                             + " (pos_seq, key_area_seq, description, weight, created_by, created_when, evaluation_list_seq, record_status, isactive) "
                             + " VALUES ( @pos_seq, @key_area, @description, @weight, @create_by, GETDATE(), 1, @status, 1) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteKeyArea(int kpa_seq, string created_by)
        {
            string strSQL = String.Empty;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@kpa_seq", kpa_seq);
                dbManager.AddParameters(1, "@updated_by", created_by);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "UPDATE hrm_perf_key_performance_area "
                         + " SET record_status = @status,"
                         + " updated_by = @updated_by,"
                         + " updated_when = GETDATE()"
                         + " WHERE kpa_seq = @kpa_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
